package br.ita.redecasd.mi.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import static br.ita.redecasd.mi.db.DBConstants.*;
import java.sql.SQLException;



public class Session
{
	private Connection conn;
	private static Session singletonSession;
	
	private Session() throws ClassNotFoundException, SQLException {
	    Class.forName(JDBC_DRIVER);
	    conn = DriverManager.getConnection( DATABASE_URL, USERNAME, PASSWORD );
	    
	}
	
	public static Session getInstance()
	throws ClassNotFoundException, SQLException {
	    if( singletonSession  == null )
	    {
	        singletonSession = new Session();
	    }
	    
	    return singletonSession;
	}
	
	
	public void createSession( String userAgent )
	throws SQLException {
	    if( exists( userAgent ))
	        updateSessionTime( userAgent );
	    else
	    {
	        String query = "Insert into Session( userAgent, lastAccess ) values ( ?, NOW() )";
	        PreparedStatement pst = conn.prepareStatement( query );
	        pst.setString( 1, userAgent );
	        pst.execute();
	        pst.close();
	    }
	    
	}
	
	public boolean validateSession( String userAgent )
	throws SQLException {
	    deleteOld();
	    
	    if ( userAgent == null )
	    	return false;
	    
	    String query = "Select userAgent from Session where userAgent = ?";
	    PreparedStatement pst = conn.prepareStatement(query);
	    pst.setString(1, userAgent);
	    ResultSet rs = pst.executeQuery();
	    boolean sessionIsValid = rs.last();
	    rs.close();
	    pst.close();
	    if ( sessionIsValid )
	    {
	        updateSessionTime( userAgent );
	        return true;
	    }
	    return false;
	    
	}
	
	private void updateSessionTime( String userAgent )
	throws SQLException {
	    String query = "Update Session set lastAccess=NOW() where userAgent = ?";
	    PreparedStatement pst = conn.prepareStatement( query );
	    pst.setString( 1, userAgent );
	    pst.execute();
	    pst.close();
	}
	
	private boolean exists( String userAgent )
	throws SQLException {
	    String query = "Select userAgent from Session where userAgent = ?";
	    PreparedStatement pst = conn.prepareStatement( query );
	    pst.setString( 1, userAgent );
	    ResultSet rs = pst.executeQuery();
	    boolean sessionExist = rs.last();
	    rs.close();
	    pst.close();
	    return sessionExist;
	}
	
	private void deleteOld() throws SQLException
	{
	    String query = "delete from Session where DATE_SUB( NOW(), INTERVAL ? MINUTE ) > lastAccess";
	    PreparedStatement pst = conn.prepareStatement( query );
	    pst.setInt( 1, SESSION_EXPIRY_TIME );
	    pst.execute();
	    pst.close();
	    
	}
	
	private void cleanOldACL() throws SQLException
	{
		String query = "delete from Patients where DATE_SUB( NOW(), INTERVAL ? MINUTE ) > timeStamp";
	    PreparedStatement pst = conn.prepareStatement( query );
	    pst.setInt( 1, SESSION_EXPIRY_TIME );
	    pst.execute();
	    pst.close();
	}
	
	public boolean physistCanGetPatientDetails( String physitian, String patient ) throws SQLException
    {
		cleanOldACL();
    	String query = "Select login from Patients where med_login = ? and login = ?";
    	PreparedStatement st = conn.prepareStatement(query);
    	st.setString(1, physitian );
    	st.setString(2, patient);
    	ResultSet rs = st.executeQuery();
    	boolean foundResult;
    	foundResult = rs.next();
    	rs.close();
    	st.close();
    	
    		
    	
    	return !foundResult;
    }
    
    public void giveMedAccessToPatientsRecords(String med_login, String pat_login ) throws SQLException
    {
    	String query = "Select login from Patients where login = ?";
    	PreparedStatement st = conn.prepareStatement(query);
    	st.setString(1, pat_login);
    	ResultSet rs = st.executeQuery();
    	boolean patientExists = rs.next();
    	rs.close();
    	st.close();
    	
    	if ( patientExists )
    		query = "update Patients set med_login = ?, timeStamp = now() where login = ?";
    	else
    		query = "insert into Patients(med_login, login, timeStamp) values ( ?, ?, now() )";
    	
    	st = conn.prepareStatement(query);
    	st.setString(1, med_login);
    	st.setString(2, pat_login);
    	st.execute();
    	
    	
    	
    }

}
